﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MockPrjTeam1.Entity;
using SD.Commons.Helpers;
using System.Collections.ObjectModel;
using System.Data.SqlClient;

namespace MockPrjTeam1.DAO
{
    public class SearchDAO
    {
        public List<Search> Load()
        {
            var list = new List<Search>();

            var results = SqlHelper.ExecuteReader("GetAllAddress");

            while (results.Read())
            {
                var item = new Search();
                item.addressID = results.GetInt32(0);
                item.address = results.GetString(5);
                item.postCode = results.GetString(4);
                item.town = results.GetString(6);
                item.county = results.GetString(7);
                item.country = results.GetString(8);
                list.Add(item);
            }

            return list;
        }
        public List<Search> Search(string PostCode, string Street, string Town)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", PostCode));
            temp.Add(new SqlParameter("p2", Street));
            temp.Add(new SqlParameter("p3", Town));

            var SearchList = new List<Search>();
            var results = SqlHelper.ExecuteReader("SearchAddress", temp);
            while (results.Read())
            {
                var itemSearch = new Search();
                itemSearch.addressID = results.GetInt32(0);
                itemSearch.address = results.GetString(5);
                itemSearch.postCode = results.GetString(4);
                itemSearch.town = results.GetString(6);
                itemSearch.county = results.GetString(7);
                itemSearch.country = results.GetString(8);
                SearchList.Add(itemSearch);
            }
            return SearchList;
        }
        public Search GetOne(int id)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", id));

            var SearchOne = new Search();
            var results = SqlHelper.ExecuteReader("GetOneAddress", temp);
            if (results.Read())
            {
                SearchOne.addressID = results.GetInt32(0);
                SearchOne.address = results.GetString(5);
                SearchOne.postCode = results.GetString(4);
                SearchOne.town = results.GetString(6);
                SearchOne.county = results.GetString(7);
                SearchOne.country = results.GetString(8);
                return SearchOne;
            }
            return null;
        }
        public bool EditAddress(Search Entity)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", Entity.addressID));
            temp.Add(new SqlParameter("p2", Entity.address));
            temp.Add(new SqlParameter("p3", Entity.postCode));
            temp.Add(new SqlParameter("p4", Entity.town));
            temp.Add(new SqlParameter("p5", Entity.county));
            temp.Add(new SqlParameter("p6", Entity.country));
            temp.Add(new SqlParameter("p7", Entity.townID));
            temp.Add(new SqlParameter("p8", Entity.countyID));
            temp.Add(new SqlParameter("p9", Entity.countryID));

            var result = SqlHelper.ExecuteNonQuery("EditAddress", temp);
            if (result != 0)
            {
                return true;
            }
            else return false;
        }
        public bool CreateAddress(Search Entity)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", Entity.addressID));
            temp.Add(new SqlParameter("p2", Entity.address));
            temp.Add(new SqlParameter("p3", Entity.postCode));
            temp.Add(new SqlParameter("p4", Entity.town));
            temp.Add(new SqlParameter("p5", Entity.county));
            temp.Add(new SqlParameter("p6", Entity.country));
            temp.Add(new SqlParameter("p7", Entity.townID));
            temp.Add(new SqlParameter("p8", Entity.countyID));
            temp.Add(new SqlParameter("p9", Entity.countryID));

            var result = SqlHelper.ExecuteNonQuery("CreateAddress", temp);
            if (result != 0)
            {
                return true;
            }
            else return false;
        }
        public int GetIdAddress()
        {
            //public int[] A = new int[];
            var result = SqlHelper.ExecuteReader("GetIdAddress");
            if (result.Read())
                return result.GetInt32(0);
            else return 0;
        }
        public Search GetFKAddress(String Town)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", Town));

            var FK = new Search();
            var result = SqlHelper.ExecuteReader("GetFKAddress", temp);
            if (result.Read())
            {
                FK.townID = result.GetInt32(0);
                FK.countyID = result.GetInt32(1);
                FK.countryID = result.GetInt32(2);
                return FK;
            }
            return null;
        }
        public List<Search> GetTown(String County)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", County));

            var list = new List<Search>();
            var results = SqlHelper.ExecuteReader("GetTown", temp);
            while (results.Read())
            {
                var item = new Search();
                item.town = results.GetString(0);
                list.Add(item);
            }

            return list;
        }
        public Search GetCounty(int ID)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", ID));

            var SearchOne = new Search();
            var result = SqlHelper.ExecuteReader("GetCountyAddress", temp);
            if (result.Read())
            {
                SearchOne.county = result.GetString(1);
                return SearchOne;
            }
            return null;
        }
        public List<Search> GetCountry()
        {
            var list = new List<Search>();
            var results = SqlHelper.ExecuteReader("GetCountryAddress");
            while (results.Read())
            {
                var item = new Search();
                item.country = results.GetString(0);
                list.Add(item);
            }

            return list;
        }
        public List<Search> GetCounty(string Country)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", Country));

            var list = new List<Search>();
            var results = SqlHelper.ExecuteReader("GetCountyList", temp);
            while (results.Read())
            {
                var item = new Search();
                item.county = results.GetString(0);
                list.Add(item);
            }

            return list;
        }
        public bool ValidPostCode(int ID, string PostCode)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", ID));
            temp.Add(new SqlParameter("p2", PostCode));

            var valid = SqlHelper.ExecuteReader("GetPostCodeAddress", temp);
            if (valid.Read())
            {
                return true;
            }
            return false;
        }
        public List<Search> GetAll()
        {
            var list = new List<Search>();

            var results = SqlHelper.ExecuteReader("GetAllBusiness");

            while (results.Read())
            {
                var item = new Search();
                item.businessID = results.GetInt32(0);
                item.businessName = results.GetString(1);
                item.sicCode = results.GetString(2);                
                list.Add(item);
            }

            return list;
        }
        public List<Search> SearchBusiness(string BusinessName, string SicCode)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", BusinessName));
            temp.Add(new SqlParameter("p2", SicCode));

            var SearchList = new List<Search>();
            var results = SqlHelper.ExecuteReader("SearchBusiness", temp);
            while (results.Read())
            {
                var itemSearch = new Search();
                itemSearch.businessID = results.GetInt32(0);
                itemSearch.businessName = results.GetString(1);
                itemSearch.sicCode = results.GetString(2);
                SearchList.Add(itemSearch);
            }
            return SearchList;
        }
        public Search GetOneBusiness(int id)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", id));

            var SearchOne = new Search();
            var results = SqlHelper.ExecuteReader("GetOneBusiness", temp);
            if (results.Read())
            {
                SearchOne.businessID = results.GetInt32(0);
                SearchOne.businessName = results.GetString(1);
                SearchOne.sicCode = results.GetString(2);                
                return SearchOne;
            }
            return null;
        }
        public bool EditBusiness(Search Entity)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", Entity.businessID));
            temp.Add(new SqlParameter("p2", Entity.businessName));
            temp.Add(new SqlParameter("p3", Entity.sicCode));            

            var result = SqlHelper.ExecuteNonQuery("EditBusiness", temp);
            if (result != 0)
            {
                return true;
            }
            else return false;
        }
        public bool CreateBusiness(Search Entity)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", Entity.businessID));
            temp.Add(new SqlParameter("p2", Entity.businessName));
            temp.Add(new SqlParameter("p3", Entity.sicCode)); 
            
            var result = SqlHelper.ExecuteNonQuery("CreateBusiness", temp);
            if (result != 0)
            {
                return true;
            }
            else return false;
        }
        public int GetIdBusiness()
        {            
            var result = SqlHelper.ExecuteReader("GetIdBusiness");
            if (result.Read())
                return result.GetInt32(0);
            else return 0;
        }
        public bool ValidSicCode(int ID, string SicCode)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", ID));
            temp.Add(new SqlParameter("p2", SicCode));

            var valid = SqlHelper.ExecuteReader("GetSicCode", temp);
            if (valid.Read())
            {
                return true;
            }
            return false;
        }
    }
}